package com.movie.util;

import com.movie.model.Movie;
import com.movie.model.Screening;
import com.movie.model.Ticket;
import com.movie.service.DataService;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class DataUtil implements DataService {

    private DBUtil dbUtil = new DBUtil();

    @Override
    public List<Movie> loadMovies() {
        List<Movie> movies = new ArrayList<>();
        String sql = "SELECT id, title, director, genre, duration FROM movies";
        try (Connection connection = dbUtil.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql);
             ResultSet resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
                Movie movie = new Movie(
                        resultSet.getString("id"),
                        resultSet.getString("title"),
                        resultSet.getString("director"),
                        resultSet.getString("genre"),
                        resultSet.getInt("duration")
                );
                movies.add(movie);
            }
        } catch (SQLException e) {
            System.err.println("Error loading movies from database: " + e.getMessage());
        }
        return movies;
    }

    @Override
    public void saveMovies(List<Movie> movies) {
        String sql = "INSERT INTO movies (id, title, director, genre, duration) VALUES (?, ?, ?, ?, ?) " +
                     "ON DUPLICATE KEY UPDATE title = VALUES(title), director = VALUES(director), genre = VALUES(genre), duration = VALUES(duration)";

        try (Connection connection = dbUtil.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            for (Movie movie : movies) {
                preparedStatement.setString(1, movie.getId());
                preparedStatement.setString(2, movie.getTitle());
                preparedStatement.setString(3, movie.getDirector());
                preparedStatement.setString(4, movie.getGenre());
                preparedStatement.setInt(5, movie.getDuration());
                preparedStatement.addBatch();
            }
           preparedStatement.executeBatch();
        } catch (SQLException e) {
            System.err.println("Error saving movies to database: " + e.getMessage());
        }
    }

    @Override
    public void deleteMovie(String movieId) {
       String sql = "DELETE FROM movies WHERE id = ?";
       try (Connection connection = dbUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
           preparedStatement.setString(1,movieId);
           preparedStatement.executeUpdate();
       }catch (SQLException e){
           System.err.println("Error deleting movie from database: " + e.getMessage());
       }
    }

    @Override
    public List<Screening> loadScreenings() {
        List<Screening> screenings = new ArrayList<>();
        String sql = "SELECT id, m_id, start_time, price, seats_available FROM screenings";
        try (Connection connection = dbUtil.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql);
             ResultSet resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
                Screening screening = new Screening(
                        resultSet.getString("id"),
                        resultSet.getString("m_id"),
                        resultSet.getObject("start_time", LocalDateTime.class),
                        resultSet.getDouble("price"),
                        resultSet.getInt("seats_available")
                );
                screenings.add(screening);
            }
        } catch (SQLException e) {
            System.err.println("Error loading screenings from database: " + e.getMessage());
        }
        return screenings;
    }

     @Override
    public void saveScreenings(List<Screening> screenings) {
       String sql = "INSERT INTO screenings (id, m_id, start_time, price, seats_available) VALUES (?, ?, ?, ?, ?) " +
                     "ON DUPLICATE KEY UPDATE m_id = VALUES(m_id), start_time = VALUES(start_time), price = VALUES(price), seats_available = VALUES(seats_available)";
        try (Connection connection = dbUtil.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            for (Screening screening : screenings) {
                preparedStatement.setString(1, screening.getId());
                preparedStatement.setString(2, screening.getMovieId());
                preparedStatement.setObject(3, screening.getStartTime());
                preparedStatement.setDouble(4, screening.getPrice());
                preparedStatement.setInt(5, screening.getSeatsAvailable());
                preparedStatement.addBatch();
            }
            preparedStatement.executeBatch();
        } catch (SQLException e) {
            System.err.println("Error saving screenings to database: " + e.getMessage());
        }
    }

    @Override
     public List<Ticket> loadTickets() {
       List<Ticket> tickets = new ArrayList<>();
         String sql = "SELECT id, screening_id, seat_number FROM tickets";
         try (Connection connection = dbUtil.getConnection();
              PreparedStatement preparedStatement = connection.prepareStatement(sql);
              ResultSet resultSet = preparedStatement.executeQuery()) {
               while (resultSet.next()) {
                   Ticket ticket = new Ticket(
                           resultSet.getString("id"),
                           resultSet.getString("screening_id"),
                            resultSet.getInt("seat_number")
                    );
                   tickets.add(ticket);
                }
          } catch (SQLException e) {
               System.err.println("Error loading tickets from database: " + e.getMessage());
          }
          return tickets;
    }

    @Override
    public void saveTickets(List<Ticket> tickets) {
        String sql = "INSERT INTO tickets (id, screening_id, seat_number) VALUES (?, ?, ?) " +
                "ON DUPLICATE KEY UPDATE screening_id = VALUES(screening_id), seat_number = VALUES(seat_number)";

         try (Connection connection = dbUtil.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            for (Ticket ticket : tickets) {
               preparedStatement.setString(1, ticket.getId());
               preparedStatement.setString(2, ticket.getScreeningId());
               preparedStatement.setInt(3, ticket.getSeatNumber());
               preparedStatement.addBatch();
            }
            preparedStatement.executeBatch();
         } catch (SQLException e) {
           System.err.println("Error saving tickets to database: " + e.getMessage());
         }
    }

    @Override
    public void deleteTicket(String ticketId) {
        String sql = "DELETE FROM tickets WHERE id = ?";
        try (Connection connection = dbUtil.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setString(1,ticketId);
            preparedStatement.executeUpdate();
        }catch (SQLException e){
            System.err.println("Error deleting ticket from database: " + e.getMessage());
        }
    }
}